﻿using System;
using System.Web;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using AuthorizeNet;
using System.Collections.Specialized;
using System.Security.Cryptography;

namespace Adams.Services.CreditCards
{
    class Program
    {
        static void Main(string[] args)
        {
            ProcessQuickDonations();
            ProcessOneTimeDonations();
            ProcessRecurringDonations();
            //RunTestTransaction();
        }

        public static void ProcessOneTimeDonations()
        {
            string connectionstring = Shared.Config.GetAppSetting("ConnectionString");
            string privatekey = Shared.Config.GetAppSetting("RSAPrivateKey");
            string apiloginid = Shared.Config.GetAppSetting("APILoginID");
            string transactionkey = Shared.Config.GetAppSetting("TransactionKey");
            bool testmode = Shared.Config.GetAppSetting("TestMode").ToBool();

            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                using (SqlCommand command = new SqlCommand(@"
SELECT 
    O.DonationID, O.BucketID, O.MemberID, O.Amount, O.EnteredOn,
    B.Name AS BucketName, 
    M.Email,
    C.FirstName, C.LastName, C.Street, C.Street2, C.City, C.State, C.ZipCode, C.Phone,
    C.CardNumber, C.CardLast4Digits, C.CardExpireMonth, C.CardExpireYear
FROM Donations.OneTime O
INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID
INNER JOIN Membership.Members M ON O.MemberID = M.MemberID
INNER JOIN Finance.CreditCards C ON C.MemberID = O.MemberID
WHERE O.StatusID = 2
", connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                int TransactionID = 0;
                                
                                #region Get Info
                                int TypeID = 1;
                                int DonationID = reader["DonationID"].ToInt();
                                int BucketID = reader["BucketID"].ToInt();
                                int MemberID = reader["MemberID"].ToInt();
                                decimal Amount = reader["Amount"].ToDecimal();
                                string BucketName = reader["BucketName"].ToString();
                                string FirstName = reader["FirstName"].ToString();
                                string LastName = reader["LastName"].ToString();
                                string Email = reader["Email"].ToString();
                                string Street = reader["Street"].ToString();
                                string Street2 = reader["Street2"].ToString();
                                string City = reader["City"].ToString();
                                string State = reader["State"].ToString();
                                string ZipCode = reader["ZipCode"].ToString();
                                string Phone = reader["Phone"].ToString();
                                string Number = reader["CardNumber"].ToString();
                                string Last4Digits = reader["CardLast4Digits"].ToString();
                                string ExpireMonth = reader["CardExpireMonth"].ToString();
                                string ExpireYear = reader["CardExpireYear"].ToString();
                                DateTime EnteredOn = reader["EnteredOn"].ToDateTime();

                                #endregion

                                string AuthInvoiceNum = string.Format("Type:{0},ID:{1},Bucket:{2},TransactionID:{3}", TypeID, DonationID, BucketName, TransactionID);
                                string Expiredate = (ExpireMonth.Length == 1 ? "0" : "") + ExpireMonth + (ExpireYear.Substring(2).Length == 1 ? "0" : "") + ExpireYear.Substring(2);
                                string Description = "One Time Donation to " + BucketName;
                                string CreditNumber = Shared.Crypt.RsaDecrypt2(Number, privatekey);

                                #region Store Transaction
                                string query = @"
INSERT INTO Finance.Transactions(
    TypeID,
	ReferenceID,
	MemberID,
    Amount,
	CreditCardNumber,
	CreditCardExpireMonth,
	CreditCardExpireYear,
	AuthAddress,
	AuthCity,
	AuthState,
	AuthZip,
	AuthEmail,
	AuthFirstName,
	AuthLastName,
	AuthPhone,
	AuthCustId
)
VALUES(
    @TypeID,
	@ReferenceID,
	@MemberID,
    @Amount,
	@CreditCardNumber,
	@CreditCardExpireMonth,
	@CreditCardExpireYear,
	@AuthAddress,
	@AuthCity,
	@AuthState,
	@AuthZip,
	@AuthEmail,
	@AuthFirstName,
	@AuthLastName,
	@AuthPhone,
	@AuthCustId
)
SELECT SCOPE_IDENTITY();
";

                                using (SqlConnection connection2 = new SqlConnection(connectionstring))
                                {
                                    connection2.Open();
                                    using (SqlCommand cmd = new SqlCommand(query, connection2))
                                    {
                                        cmd.Parameters.Add(new SqlParameter("@TypeID", TypeID));
                                        cmd.Parameters.Add(new SqlParameter("@ReferenceID", DonationID));
                                        cmd.Parameters.Add(new SqlParameter("@MemberID", MemberID));
                                        cmd.Parameters.Add(new SqlParameter("@Amount", Amount));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardNumber", Number));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardExpireMonth", ExpireMonth));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardExpireYear", ExpireYear));
                                        cmd.Parameters.Add(new SqlParameter("@AuthAddress", Street + " " + Street2));
                                        cmd.Parameters.Add(new SqlParameter("@AuthCity", City));
                                        cmd.Parameters.Add(new SqlParameter("@AuthState", State));
                                        cmd.Parameters.Add(new SqlParameter("@AuthZip", ZipCode));
                                        cmd.Parameters.Add(new SqlParameter("@AuthEmail", Email));
                                        cmd.Parameters.Add(new SqlParameter("@AuthFirstName", FirstName));
                                        cmd.Parameters.Add(new SqlParameter("@AuthLastName", LastName));
                                        cmd.Parameters.Add(new SqlParameter("@AuthPhone", Phone));
                                        cmd.Parameters.Add(new SqlParameter("@AuthCustId", MemberID));
                                        TransactionID = cmd.ExecuteScalar().ToInt();
                                    }
                                    connection2.Close();
                                }
#endregion

                                #region Charge Card
                                var request = new AuthorizationRequest(CreditNumber, Expiredate, Amount, Description);
                                request.InvoiceNum = AuthInvoiceNum;
                                request.Address = Street + " " + Street2;
                                request.City = City;
                                request.State = State;
                                request.Zip = ZipCode;
                                //request.Email = Email;
                                request.FirstName = FirstName;
                                request.LastName = LastName;
                                request.Phone = Phone;
                                request.CustId = MemberID.ToString();

                                var gate = new Gateway(apiloginid, transactionkey, testmode);
                                var response = gate.Send(request);
                                #endregion

                                #region Store Results
                                query = @"
UPDATE Finance.Transactions SET 
    AuthInvoiceNum = @AuthInvoiceNum,
    ResponseApproved = @ResponseApproved,
    ResponseTransactionID = @ResponseTransactionID,
    ResponseResponseCode = @ResponseResponseCode,
    ResponseAuthorizationCode = @ResponseAuthorizationCode,
    ResponseMessage = @ResponseMessage
WHERE TransactionID = @TransactionID

UPDATE Donations.OneTime SET
    StatusID = @StatusID
WHERE DonationID = @DonationID
    
";
                                int StatusID = response.Approved ? 3 : 5;

                                using (SqlConnection connection3 = new SqlConnection(connectionstring))
                                {
                                    connection3.Open();
                                    using (SqlCommand cmd = new SqlCommand(query, connection3))
                                    {
                                        cmd.Parameters.Add(new SqlParameter("@TransactionID", TransactionID));
                                        cmd.Parameters.Add(new SqlParameter("@StatusID", StatusID));
                                        cmd.Parameters.Add(new SqlParameter("@DonationID", DonationID));
                                        cmd.Parameters.Add(new SqlParameter("@AuthInvoiceNum", AuthInvoiceNum));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseApproved", response.Approved));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseTransactionID", response.TransactionID));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseAuthorizationCode", response.AuthorizationCode));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseMessage", response.Message));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseResponseCode", response.ResponseCode));
                                        cmd.ExecuteNonQuery();
                                    }
                                    connection3.Close();
                                }
#endregion

                                if (response.ResponseCode == "1")
                                {
                                    #region Email Reciept
                                    string subject = "Receipt for your donation!";
                                    string from = Shared.Config.GetAppSetting("SecurityFromEmail");
                                    string templatepath = Shared.Config.GetAppSetting("EmailTemplatesPath").TrimEnd('\\') + "\\onetimedonationreceipt.htm";

                                    string body = Shared.IOHelper.GetFileContents(templatepath);
                                    body = body.Replace("[BUCKETNAME]", BucketName);
                                    body = body.Replace("[AMOUNT]", string.Format("{0:C}", Amount));
                                    body = body.Replace("[FIRSTNAME]", FirstName);
                                    body = body.Replace("[ENTEREDON]", EnteredOn.ToShortDateString());
                                    body = body.Replace("[PROCESSEDDON]", DateTime.Now.ToShortDateString());
                                    body = body.Replace("[TRANSACTIONID]", TransactionID.ToString());

                                    query = "usp_QueueEmail";
                                    using (SqlConnection connection4 = new SqlConnection(connectionstring))
                                    {
                                        connection4.Open();
                                        using (SqlCommand cmd = new SqlCommand(query, connection4))
                                        {
                                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@Subject", subject));
                                            cmd.Parameters.Add(new SqlParameter("@From", from));
                                            cmd.Parameters.Add(new SqlParameter("@To", Email));
                                            cmd.Parameters.Add(new SqlParameter("@Body", body));
                                            cmd.Parameters.Add(new SqlParameter("@Priority", 1));
                                            cmd.ExecuteNonQuery();
                                        }
                                        connection4.Close();
                                    }
                                    #endregion
                                }
                                else
                                {
                                    #region Email Failure
                                    string subject = "Your donation was not successful!";
                                    string from = Shared.Config.GetAppSetting("SecurityFromEmail");
                                    string templatepath = Shared.Config.GetAppSetting("EmailTemplatesPath").TrimEnd('\\') + "\\onetimedonationfail.htm";

                                    string body = Shared.IOHelper.GetFileContents(templatepath);
                                    body = body.Replace("[BUCKETNAME]", BucketName);
                                    body = body.Replace("[AMOUNT]", string.Format("{0:C}", Amount));
                                    body = body.Replace("[FIRSTNAME]", FirstName);
                                    body = body.Replace("[ENTEREDON]", EnteredOn.ToShortDateString());
                                    body = body.Replace("[PROCESSEDDON]", DateTime.Now.ToShortDateString());
                                    body = body.Replace("[TRANSACTIONID]", TransactionID.ToString());
                                    body = body.Replace("[RESPONSEMESSAGE]", response.Message);

                                    query = "usp_QueueEmail";
                                    using (SqlConnection connection4 = new SqlConnection(connectionstring))
                                    {
                                        connection4.Open();
                                        using (SqlCommand cmd = new SqlCommand(query, connection4))
                                        {
                                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@Subject", subject));
                                            cmd.Parameters.Add(new SqlParameter("@From", from));
                                            cmd.Parameters.Add(new SqlParameter("@To", Email));
                                            cmd.Parameters.Add(new SqlParameter("@Body", body));
                                            cmd.Parameters.Add(new SqlParameter("@Priority", 1));
                                            cmd.ExecuteNonQuery();
                                        }
                                        connection4.Close();
                                    }
                                    #endregion
                                }
                            }
                        }
                        reader.Close();
                        connection.Close();
                    }
                }
            }
        }

        public static void ProcessRecurringDonations()
        {
            string connectionstring = Shared.Config.GetAppSetting("ConnectionString");
            string privatekey = Shared.Config.GetAppSetting("RSAPrivateKey");
            string apiloginid = Shared.Config.GetAppSetting("APILoginID");
            string transactionkey = Shared.Config.GetAppSetting("TransactionKey");
            bool testmode = Shared.Config.GetAppSetting("TestMode").ToBool();

            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                using (SqlCommand command = new SqlCommand(@"
SELECT 
    O.DonationID, O.BucketID, O.MemberID, O.Amount,
    B.Name AS BucketName,
    M.Email,
    C.FirstName, C.LastName, C.Street, C.Street2, C.City, C.State, C.ZipCode, C.Phone,
    C.CardNumber, C.CardLast4Digits, C.CardExpireMonth, C.CardExpireYear
FROM Donations.Recurring O
INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID
INNER JOIN Membership.Members M ON O.MemberID = M.MemberID
INNER JOIN Finance.CreditCards C ON C.MemberID = O.MemberID
WHERE O.StatusID IN (2, 3, 5) 
    AND DATEDIFF(d, NextProcessOn, GETDATE()) >= 0 
    AND (LastProcessedOn IS NULL OR DATEDIFF(m, LastProcessedOn, GETDATE()) >= 1)
", connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                int TransactionID = 0;

                                #region Get Info
                                int TypeID = 2;
                                int DonationID = reader["DonationID"].ToInt();
                                int BucketID = reader["BucketID"].ToInt();
                                int MemberID = reader["MemberID"].ToInt();
                                decimal Amount = reader["Amount"].ToDecimal();
                                string BucketName = reader["BucketName"].ToString();
                                string FirstName = reader["FirstName"].ToString();
                                string LastName = reader["LastName"].ToString();
                                string Email = reader["Email"].ToString();
                                string Street = reader["Street"].ToString();
                                string Street2 = reader["Street2"].ToString();
                                string City = reader["City"].ToString();
                                string State = reader["State"].ToString();
                                string ZipCode = reader["ZipCode"].ToString();
                                string Phone = reader["Phone"].ToString();
                                string Number = reader["CardNumber"].ToString();
                                string Last4Digits = reader["CardLast4Digits"].ToString();
                                string ExpireMonth = reader["CardExpireMonth"].ToString();
                                string ExpireYear = reader["CardExpireYear"].ToString();
                                #endregion

                                string AuthInvoiceNum = string.Format("Type:{0},ID:{1},Bucket:{2},TransactionID:{3}", TypeID, DonationID, BucketName, TransactionID);
                                string Expiredate = (ExpireMonth.Length == 1 ? "0" : "") + ExpireMonth + (ExpireYear.Substring(2).Length == 1 ? "0" : "") + ExpireYear.Substring(2);
                                string Description = "Recurring Donation to " + BucketName;
                                string CreditNumber = Shared.Crypt.RsaDecrypt2(Number, privatekey);

                                #region Store Transaction
                                string query = @"
INSERT INTO Finance.Transactions(
    TypeID,
	ReferenceID,
	MemberID,
    Amount,
	CreditCardNumber,
	CreditCardExpireMonth,
	CreditCardExpireYear,
	AuthAddress,
	AuthCity,
	AuthState,
	AuthZip,
	AuthEmail,
	AuthFirstName,
	AuthLastName,
	AuthPhone,
	AuthCustId
)
VALUES(
    @TypeID,
	@ReferenceID,
	@MemberID,
    @Amount,
	@CreditCardNumber,
	@CreditCardExpireMonth,
	@CreditCardExpireYear,
	@AuthAddress,
	@AuthCity,
	@AuthState,
	@AuthZip,
	@AuthEmail,
	@AuthFirstName,
	@AuthLastName,
	@AuthPhone,
	@AuthCustId
)
SELECT SCOPE_IDENTITY();
";

                                using (SqlConnection connection2 = new SqlConnection(connectionstring))
                                {
                                    connection2.Open();
                                    using (SqlCommand cmd = new SqlCommand(query, connection2))
                                    {
                                        cmd.Parameters.Add(new SqlParameter("@TypeID", TypeID));
                                        cmd.Parameters.Add(new SqlParameter("@ReferenceID", DonationID));
                                        cmd.Parameters.Add(new SqlParameter("@MemberID", MemberID));
                                        cmd.Parameters.Add(new SqlParameter("@Amount", Amount));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardNumber", Number));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardExpireMonth", ExpireMonth));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardExpireYear", ExpireYear));
                                        cmd.Parameters.Add(new SqlParameter("@AuthAddress", Street + " " + Street2));
                                        cmd.Parameters.Add(new SqlParameter("@AuthCity", City));
                                        cmd.Parameters.Add(new SqlParameter("@AuthState", State));
                                        cmd.Parameters.Add(new SqlParameter("@AuthZip", ZipCode));
                                        cmd.Parameters.Add(new SqlParameter("@AuthEmail", Email));
                                        cmd.Parameters.Add(new SqlParameter("@AuthFirstName", FirstName));
                                        cmd.Parameters.Add(new SqlParameter("@AuthLastName", LastName));
                                        cmd.Parameters.Add(new SqlParameter("@AuthPhone", Phone));
                                        cmd.Parameters.Add(new SqlParameter("@AuthCustId", MemberID));
                                        TransactionID = cmd.ExecuteScalar().ToInt();
                                    }
                                    connection2.Close();
                                }
                                #endregion


                                #region Charge Card
                                var request = new AuthorizationRequest(CreditNumber, Expiredate, Amount, Description);
                                request.InvoiceNum = AuthInvoiceNum;
                                request.Address = Street + " " + Street2;
                                request.City = City;
                                request.State = State;
                                request.Zip = ZipCode;
                                //request.Email = Email;
                                request.FirstName = FirstName;
                                request.LastName = LastName;
                                request.Phone = Phone;
                                request.CustId = MemberID.ToString();

                                var gate = new Gateway(apiloginid, transactionkey, testmode);
                                var response = gate.Send(request);
                                #endregion

                                #region Store Results
                                query = @"
UPDATE Finance.Transactions SET 
    AuthInvoiceNum = @AuthInvoiceNum,
    ResponseApproved = @ResponseApproved,
    ResponseTransactionID = @ResponseTransactionID,
    ResponseResponseCode = @ResponseResponseCode,
    ResponseAuthorizationCode = @ResponseAuthorizationCode,
    ResponseMessage = @ResponseMessage
WHERE TransactionID = @TransactionID

UPDATE Donations.Recurring SET
    StatusID = @StatusID,
    NextProcessOn = @NextProcessOn, 
    LastProcessedOn = @LastProcessedOn
WHERE DonationID = @DonationID
    
";
                                int StatusID = response.Approved ? 3 : 5;
                                DateTime LastProcessedOn = DateTime.Now;
                                DateTime NextProcessOn = DateTime.Now.AddMonths(1);

                                using (SqlConnection connection3 = new SqlConnection(connectionstring))
                                {
                                    connection3.Open();
                                    using (SqlCommand cmd = new SqlCommand(query, connection3))
                                    {
                                        cmd.Parameters.Add(new SqlParameter("@TransactionID", TransactionID));
                                        cmd.Parameters.Add(new SqlParameter("@StatusID", StatusID));
                                        cmd.Parameters.Add(new SqlParameter("@AuthInvoiceNum", AuthInvoiceNum));
                                        cmd.Parameters.Add(new SqlParameter("@NextProcessOn", NextProcessOn));
                                        cmd.Parameters.Add(new SqlParameter("@LastProcessedOn", LastProcessedOn));
                                        cmd.Parameters.Add(new SqlParameter("@DonationID", DonationID));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseApproved", response.Approved));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseTransactionID", response.TransactionID));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseAuthorizationCode", response.AuthorizationCode));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseMessage", response.Message));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseResponseCode", response.ResponseCode));
                                        cmd.ExecuteNonQuery();
                                    }
                                    connection3.Close();
                                }
                                #endregion

                                if (response.ResponseCode == "1")
                                {
                                    #region Email Reciept
                                    string subject = "Receipt for your donation!";
                                    string from = Shared.Config.GetAppSetting("SecurityFromEmail");
                                    string templatepath = Shared.Config.GetAppSetting("EmailTemplatesPath").TrimEnd('\\') + "\\recurringdonationreceipt.htm";

                                    string body = Shared.IOHelper.GetFileContents(templatepath);
                                    body = body.Replace("[BUCKETNAME]", BucketName);
                                    body = body.Replace("[AMOUNT]", string.Format("{0:C}", Amount));
                                    body = body.Replace("[FIRSTNAME]", FirstName);
                                    body = body.Replace("[PROCESSEDDON]", DateTime.Now.ToShortDateString());
                                    body = body.Replace("[TRANSACTIONID]", TransactionID.ToString());

                                    query = "usp_QueueEmail";
                                    using (SqlConnection connection4 = new SqlConnection(connectionstring))
                                    {
                                        connection4.Open();
                                        using (SqlCommand cmd = new SqlCommand(query, connection4))
                                        {
                                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@Subject", subject));
                                            cmd.Parameters.Add(new SqlParameter("@From", from));
                                            cmd.Parameters.Add(new SqlParameter("@To", Email));
                                            cmd.Parameters.Add(new SqlParameter("@Body", body));
                                            cmd.Parameters.Add(new SqlParameter("@Priority", 1));
                                            cmd.ExecuteNonQuery();
                                        }
                                        connection4.Close();
                                    }

                                    #endregion
                                }
                                else
                                {
                                    #region Email Failure
                                    string subject = "Your recurring donation was not successful!";
                                    string from = Shared.Config.GetAppSetting("SecurityFromEmail");
                                    string templatepath = Shared.Config.GetAppSetting("EmailTemplatesPath").TrimEnd('\\') + "\\recurringdonationfail.htm";

                                    string body = Shared.IOHelper.GetFileContents(templatepath);
                                    body = body.Replace("[BUCKETNAME]", BucketName);
                                    body = body.Replace("[AMOUNT]", string.Format("{0:C}", Amount));
                                    body = body.Replace("[FIRSTNAME]", FirstName);
                                    body = body.Replace("[PROCESSEDDON]", DateTime.Now.ToShortDateString());
                                    body = body.Replace("[TRANSACTIONID]", TransactionID.ToString());
                                    body = body.Replace("[RESPONSEMESSAGE]", response.Message);

                                    query = "usp_QueueEmail";
                                    using (SqlConnection connection4 = new SqlConnection(connectionstring))
                                    {
                                        connection4.Open();
                                        using (SqlCommand cmd = new SqlCommand(query, connection4))
                                        {
                                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@Subject", subject));
                                            cmd.Parameters.Add(new SqlParameter("@From", from));
                                            cmd.Parameters.Add(new SqlParameter("@To", Email));
                                            cmd.Parameters.Add(new SqlParameter("@Body", body));
                                            cmd.Parameters.Add(new SqlParameter("@Priority", 1));
                                            cmd.ExecuteNonQuery();
                                        }
                                        connection4.Close();
                                    }
                                    #endregion
                                }
                            }
                        }
                        reader.Close();
                        connection.Close();
                    }
                }
            }
        }

        public static void ProcessQuickDonations()
        {
            string connectionstring = Shared.Config.GetAppSetting("ConnectionString");
            string privatekey = Shared.Config.GetAppSetting("RSAPrivateKey");
            string apiloginid = Shared.Config.GetAppSetting("APILoginID");
            string transactionkey = Shared.Config.GetAppSetting("TransactionKey");
            bool testmode = Shared.Config.GetAppSetting("TestMode").ToBool();

            using (SqlConnection connection = new SqlConnection(connectionstring))
            {
                using (SqlCommand command = new SqlCommand(@"
SELECT O.*, B.Name AS BucketName
FROM Donations.Quick O
INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID
WHERE O.StatusID = 2
", connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                int TransactionID = 0;

                                #region Get Info
                                int TypeID = 3;
                                int DonationID = reader["DonationID"].ToInt();
                                int BucketID = reader["BucketID"].ToInt();
                                decimal Amount = reader["Amount"].ToDecimal();
                                string BucketName = reader["BucketName"].ToString();
                                string FirstName = reader["FirstName"].ToString();
                                string LastName = reader["LastName"].ToString();
                                string Email = reader["Email"].ToString();
                                string Street = reader["Street"].ToString();
                                string Street2 = reader["Street2"].ToString();
                                string City = reader["City"].ToString();
                                string State = reader["State"].ToString();
                                string ZipCode = reader["ZipCode"].ToString();
                                string Phone = reader["Phone"].ToString();
                                string Number = reader["CardNumber"].ToString();
                                string Last4Digits = reader["CardLast4Digits"].ToString();
                                string ExpireMonth = reader["CardExpireMonth"].ToString();
                                string ExpireYear = reader["CardExpireYear"].ToString();
                                DateTime EnteredOn = reader["EnteredOn"].ToDateTime();

                                #endregion

                                string AuthInvoiceNum = string.Format("Type:{0},ID:{1},Bucket:{2},TransactionID:{3}", TypeID, DonationID, BucketName, TransactionID);
                                string Expiredate = (ExpireMonth.Length == 1 ? "0" : "") + ExpireMonth + (ExpireYear.Substring(2).Length == 1 ? "0" : "") + ExpireYear.Substring(2);
                                string Description = "Quick Donation to " + BucketName;
                                string CreditNumber = Shared.Crypt.RsaDecrypt2(Number, privatekey);

                                #region Store Transaction
                                string query = @"
INSERT INTO Finance.Transactions(
    TypeID,
	ReferenceID,
    Amount,
	CreditCardNumber,
	CreditCardExpireMonth,
	CreditCardExpireYear,
	AuthAddress,
	AuthCity,
	AuthState,
	AuthZip,
	AuthEmail,
	AuthFirstName,
	AuthLastName,
	AuthPhone
)
VALUES(
    @TypeID,
	@ReferenceID,
    @Amount,
	@CreditCardNumber,
	@CreditCardExpireMonth,
	@CreditCardExpireYear,
	@AuthAddress,
	@AuthCity,
	@AuthState,
	@AuthZip,
	@AuthEmail,
	@AuthFirstName,
	@AuthLastName,
	@AuthPhone
)
SELECT SCOPE_IDENTITY();
";

                                using (SqlConnection connection2 = new SqlConnection(connectionstring))
                                {
                                    connection2.Open();
                                    using (SqlCommand cmd = new SqlCommand(query, connection2))
                                    {
                                        cmd.Parameters.Add(new SqlParameter("@TypeID", TypeID));
                                        cmd.Parameters.Add(new SqlParameter("@ReferenceID", DonationID));
                                        cmd.Parameters.Add(new SqlParameter("@Amount", Amount));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardNumber", Number));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardExpireMonth", ExpireMonth));
                                        cmd.Parameters.Add(new SqlParameter("@CreditCardExpireYear", ExpireYear));
                                        cmd.Parameters.Add(new SqlParameter("@AuthAddress", Street + " " + Street2));
                                        cmd.Parameters.Add(new SqlParameter("@AuthCity", City));
                                        cmd.Parameters.Add(new SqlParameter("@AuthState", State));
                                        cmd.Parameters.Add(new SqlParameter("@AuthZip", ZipCode));
                                        cmd.Parameters.Add(new SqlParameter("@AuthEmail", Email));
                                        cmd.Parameters.Add(new SqlParameter("@AuthFirstName", FirstName));
                                        cmd.Parameters.Add(new SqlParameter("@AuthLastName", LastName));
                                        cmd.Parameters.Add(new SqlParameter("@AuthPhone", Phone));
                                        TransactionID = cmd.ExecuteScalar().ToInt();
                                    }
                                    connection2.Close();
                                }
                                #endregion


                                #region Charge Card
                                var request = new AuthorizationRequest(CreditNumber, Expiredate, Amount, Description);
                                request.InvoiceNum = AuthInvoiceNum;
                                request.Address = Street + " " + Street2;
                                request.City = City;
                                request.State = State;
                                request.Zip = ZipCode;
                                //request.Email = Email;
                                request.FirstName = FirstName;
                                request.LastName = LastName;
                                request.Phone = Phone;

                                var gate = new Gateway(apiloginid, transactionkey, testmode);
                                var response = gate.Send(request);
                                #endregion

                                #region Store Results
                                query = @"
UPDATE Finance.Transactions SET 
    AuthInvoiceNum = @AuthInvoiceNum,
    ResponseApproved = @ResponseApproved,
    ResponseTransactionID = @ResponseTransactionID,
    ResponseResponseCode = @ResponseResponseCode,
    ResponseAuthorizationCode = @ResponseAuthorizationCode,
    ResponseMessage = @ResponseMessage
WHERE TransactionID = @TransactionID

UPDATE Donations.Quick SET
    StatusID = @StatusID
WHERE DonationID = @DonationID
    
";
                                int StatusID = response.Approved ? 3 : 5;

                                using (SqlConnection connection3 = new SqlConnection(connectionstring))
                                {
                                    connection3.Open();
                                    using (SqlCommand cmd = new SqlCommand(query, connection3))
                                    {
                                        cmd.Parameters.Add(new SqlParameter("@TransactionID", TransactionID));
                                        cmd.Parameters.Add(new SqlParameter("@StatusID", StatusID));
                                        cmd.Parameters.Add(new SqlParameter("@DonationID", DonationID));
                                        cmd.Parameters.Add(new SqlParameter("@AuthInvoiceNum", AuthInvoiceNum));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseApproved", response.Approved));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseTransactionID", response.TransactionID));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseAuthorizationCode", response.AuthorizationCode));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseMessage", response.Message));
                                        cmd.Parameters.Add(new SqlParameter("@ResponseResponseCode", response.ResponseCode));
                                        cmd.ExecuteNonQuery();
                                    }
                                    connection3.Close();
                                }
                                #endregion

                                if (response.ResponseCode == "1")
                                {
                                    #region Email Reciept
                                    string subject = "Receipt for your donation!";
                                    string from = Shared.Config.GetAppSetting("SecurityFromEmail");
                                    string templatepath = Shared.Config.GetAppSetting("EmailTemplatesPath").TrimEnd('\\') + "\\quickdonationreceipt.htm";

                                    string body = Shared.IOHelper.GetFileContents(templatepath);
                                    body = body.Replace("[BUCKETNAME]", BucketName);
                                    body = body.Replace("[AMOUNT]", string.Format("{0:C}", Amount));
                                    body = body.Replace("[FIRSTNAME]", FirstName);
                                    body = body.Replace("[ENTEREDON]", EnteredOn.ToShortDateString());
                                    body = body.Replace("[PROCESSEDDON]", DateTime.Now.ToShortDateString());
                                    body = body.Replace("[TRANSACTIONID]", TransactionID.ToString());

                                    query = "usp_QueueEmail";
                                    using (SqlConnection connection4 = new SqlConnection(connectionstring))
                                    {
                                        connection4.Open();
                                        using (SqlCommand cmd = new SqlCommand(query, connection4))
                                        {
                                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@Subject", subject));
                                            cmd.Parameters.Add(new SqlParameter("@From", from));
                                            cmd.Parameters.Add(new SqlParameter("@To", Email));
                                            cmd.Parameters.Add(new SqlParameter("@Body", body));
                                            cmd.Parameters.Add(new SqlParameter("@Priority", 1));
                                            cmd.ExecuteNonQuery();
                                        }
                                        connection4.Close();
                                    }
                                    #endregion
                                }
                                else
                                {
                                    #region Email Failure
                                    string subject = "Your donation was not successful!";
                                    string from = Shared.Config.GetAppSetting("SecurityFromEmail");
                                    string templatepath = Shared.Config.GetAppSetting("EmailTemplatesPath").TrimEnd('\\') + "\\quickdonationfail.htm";

                                    string body = Shared.IOHelper.GetFileContents(templatepath);
                                    body = body.Replace("[BUCKETNAME]", BucketName);
                                    body = body.Replace("[AMOUNT]", string.Format("{0:C}", Amount));
                                    body = body.Replace("[FIRSTNAME]", FirstName);
                                    body = body.Replace("[ENTEREDON]", EnteredOn.ToShortDateString());
                                    body = body.Replace("[PROCESSEDDON]", DateTime.Now.ToShortDateString());
                                    body = body.Replace("[TRANSACTIONID]", TransactionID.ToString());
                                    body = body.Replace("[RESPONSEMESSAGE]", response.Message);

                                    query = "usp_QueueEmail";
                                    using (SqlConnection connection4 = new SqlConnection(connectionstring))
                                    {
                                        connection4.Open();
                                        using (SqlCommand cmd = new SqlCommand(query, connection4))
                                        {
                                            cmd.CommandType = System.Data.CommandType.StoredProcedure;
                                            cmd.Parameters.Add(new SqlParameter("@Subject", subject));
                                            cmd.Parameters.Add(new SqlParameter("@From", from));
                                            cmd.Parameters.Add(new SqlParameter("@To", Email));
                                            cmd.Parameters.Add(new SqlParameter("@Body", body));
                                            cmd.Parameters.Add(new SqlParameter("@Priority", 1));
                                            cmd.ExecuteNonQuery();
                                        }
                                        connection4.Close();
                                    }
                                    #endregion
                                }
                            }
                        }
                        reader.Close();
                        connection.Close();
                    }
                }
            }
        }

        public static void RunTestTransaction()
        {
            string connectionstring = Shared.Config.GetAppSetting("ConnectionString");
            string apiloginid = Shared.Config.GetAppSetting("APILoginID");
            string transactionkey = Shared.Config.GetAppSetting("TransactionKey");

            var request = new AuthorizationRequest("4111111111111111", "1216", 10.01M, "Test Transaction");
            request.InvoiceNum = "TestInvoice123";
            request.Address = "1234 Main Street";
            request.City = "Herndon";
            request.State = "VA";
            request.Zip = "20171";
            //request.Email = "test@test.com";
            request.FirstName = "test";
            request.LastName = "user";
            request.Phone = "12345678";
            request.CustId = "custid";

            var gate = new Gateway(apiloginid, transactionkey, true);
            var response = gate.Send(request);

            Console.WriteLine("{0}: {1} : {2}", response.ResponseCode, response.TransactionID, response.Message);
            Console.Read();
        }
    }
}
